********************************************************************************
********************************************************************************
******************************* APPENDIX TABLES ********************************
********************************************************************************
********************************************************************************


********************************************************************************
* THIS FILE RECREATES THE FOLLOWING TABLES IN THE APPENDIX: 
* APPENDIX C, TABLE 3: MAIL TYPE INDICATED FOR CONGRESSIONAL SOURCE
* APPENDIX C, TABLE 4: MAIL SOURCE INDICATED FOR CONGRESSIONAL INTEREST MAIL
* APPENDIX D, TABLE 5: ZIP CODES THAT INCLUDE MULTIPLE DISTRICTS (2000)
* APPENDIX E, TABLE 6: ZIP CODES THAT INCLUDE MULTIPLE DISTRICTS (2010)
* APPENDIX I, TABLE 9: FREQUENCY OF REPEATED INQUIRIES
********************************************************************************

clear 

set more off

* SET WORKING DIRECTORY
cd "/Users/lindseygailmard/Dropbox/RegLab/BVA Congressional Intervention YLJ" 

********************************************************************************
*********** TABLE 3: MAIL TYPE INDICATED FOR CONGRESSIONAL SOURCE **************
********************************************************************************

insheet using "./Replication Files/Data/inquiries.csv"

tab mltype

destring mltype, replace force

label define mltype_label 1 "Change of Address" 2 "Congressional Interest" ///
	3 "Controlled Correspondence" 4 "CUE Related" 5 "Evidence or Argument" ///
	6 "FOIA Request" 7 "Hearing Related" 8 "Motion to Advance on Docket" ///
	9 "Motion for Reconsideration" 10 "Power of Attorney Related" ///
	11 "Professional Service Mail" 12 "Returned or Undeliverable Mail" ///
	13 "Status Inquiry" 14 "Death Certificate" 15 "Appellate Group Mail" ///
	16 "Extension Request" 17 "Privacy Act Request" 18 "Other Motion" ///
	19 "Privacy Complaints" 20 "Intra/Inter Agency Request" ///
	21 "Ebert Temporary Transfer" 22 "How Do I Appeal Pamphlet" ///
	23 "ECA Revocation" 24 "FOIA Request & Other Actions" ///
	25 "Motion to Vacate" 26 "Attorney Inquiry" 27 "Withdrawal of Appeal" 
	
label val mltype mltype_label	

tab mltype if mlsource=="E"|mlsource=="G"|mlsource=="P"|mlsource=="X"

estpost tab mltype if mlsource=="E"|mlsource=="G"|mlsource=="P"|mlsource=="X", sort
esttab, cell("b pct(fmt(a))")  collab("Frequency" "Percent") noobs nonumb nomtitle tex

********************************************************************************
******* TABLE 4: MAIL SOURCE INDICATED FOR CONGRESSIONAL INTEREST MAIL *********
********************************************************************************

gen mlsource_num = 1 if mlsource=="C"
	replace mlsource_num = 2 if mlsource=="E"
	replace mlsource_num = 3 if mlsource=="F"
	replace mlsource_num = 4 if mlsource=="G"
	replace mlsource_num = 5 if mlsource=="M"
	replace mlsource_num = 6 if mlsource=="P"
	replace mlsource_num = 7 if mlsource=="S"
	replace mlsource_num = 8 if mlsource=="W"
	replace mlsource_num = 9 if mlsource=="X"
	replace mlsource_num = 10 if mlsource=="Y"

label define mlsource_label 1 "Claims Folder" 2 "Congressional Email" ///
	3 "Non-Congressional Fax" 4 "Congressional Mail" 5 "Non-Congressional Mail" ///
	6 "Congressional Phone" 7 "Secretary of Department of Veterans' Affairs" ///
	8 "White House Mail" 9 "Congressional Fax" 10 "White House Fax"

label val mlsource_num mlsource_label

tab mlsource_num if mltype==2, sort

estpost tab mlsource_num if mltype==2, sort
esttab, cell("b pct(fmt(a))")  collab("Frequency" "Percent") noobs nonumb nomtitle tex

********************************************************************************
******* TABLE 5: ZIP CODES THAT INCLUDE MULTIPLE DISTRICTS (2000 CENSUS) *******
********************************************************************************

clear 

import delimited using "./Replication Files/Data/ZIP_CD_2010.csv"

tostring cd, gen(district)

gen state_fips = substr(district, 1, 2) if cd>999
	replace state_fips = substr(district, 1, 1) if cd<1000
	
gen district_number = substr(district, 3, 2) if cd>999	
	replace district_number = substr(district, 2, 2) if cd<1000
	
	destring(state_fips), replace

gen US_state = 0 if state_fips==72|state_fips==11
	replace US_state = 1 if missing(US_state)	
	
bysort zipcode: egen number_cds = count(cd)
bysort zipcode: egen max_res_ratio = max(res_ratio)	

distinct zipcode if max_res_ratio<=0.5&number_cds>2
distinct zipcode if max_res_ratio>0.5&number_cds>2

summ max_res_ratio if US_state==1

collapse (mean) cd = number_cds if US_state==1, by(zipcode)

estpost tab cd
esttab, cell("b pct(fmt(a))")  collab("Freq." "Percent")  noobs nonumb nomtitle tex


********************************************************************************
******* TABLE 6: ZIP CODES THAT INCLUDE MULTIPLE DISTRICTS (2010 CENSUS) *******
********************************************************************************

clear

import delimited using "./Replication Files/Data/ZIP_CD_2012.csv"

tostring cd, gen(district)

gen state_fips = substr(district, 1, 2) if cd>999
	replace state_fips = substr(district, 1, 1) if cd<1000
	
gen district_number = substr(district, 3, 2) if cd>999	
	replace district_number = substr(district, 2, 2) if cd<1000
	
	destring(state_fips), replace

gen US_state = 0 if state_fips==72|state_fips==11
	replace US_state = 1 if missing(US_state)
	
bysort zipcode: egen number_cds = count(cd)

collapse (mean) cd = number_cds if US_state==1, by(zipcode)

estpost tab cd
esttab, cell("b pct(fmt(a))")  collab("Freq." "Percent")  noobs nonumb nomtitle tex

********************************************************************************
******************* TABLE 9: FREQUENCY OF REPEATED INQUIRIES *******************
********************************************************************************

clear 

insheet using "./Replication Files/Data/inquiries.csv"

bysort appeal_id: egen total_inquiries = count(congress)

collapse (mean) mean_total_inquiries = total_inquiries, by(appeal_id)

label define inquiries_label 1 "1" 2 "2" 3 "3" 4 "4" 5 "5" 6 "6 or more"

gen total_CI = mean_total_inquiries
	replace total_CI = 6 if mean_total_inquiries>=6
	
label val total_CI inquiries_label

estpost tab total_CI

	
	
